package controller;

import io.micronaut.context.ApplicationContext;
import io.micronaut.context.annotation.Property;
import io.micronaut.context.annotation.Value;
import io.micronaut.context.env.Environment;
import io.micronaut.http.HttpResponse;
import io.micronaut.http.HttpStatus;
import io.micronaut.http.annotation.Body;
import io.micronaut.http.annotation.Controller;
import io.micronaut.http.annotation.Get;
import io.micronaut.http.annotation.Post;
import service.TestService;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

@Controller("/main")
public class MainController
{

	TestService testService ;

	String url="jdbc:postgresql://10.201.1.41:5432/estate0606";
	String username="estate";
	String password = "9FTeR5dA!";

//	String url="jdbc:postgresql://localhost:5432/estate0606";
//	String username="estate";
//	String password = "9FTeR5dA!";

//	 String url="jdbc:postgresql://10.10.20.13:5432/estate_test";
//	 String username="estate";
//	 String password = "8FTeR5dA!";


//	 String url="jdbc:postgresql://10.10.20.13:5432/estate";
//	 String username="estate";
//	 String password = "8FTeR5dA!";

//	@Property(name="ttt.name")
//	String name ;

	@Property(name = "ttt")
	Map<String, String> ttt;
	@Get("/test")
	public HttpResponse test(){


		System.out.println(ttt.get("name"));
		//testService.test();
		return HttpResponse.status(HttpStatus.OK).body("success");
	}

	@Post("/getAllId")
	public  HashMap<String,String> getAllId( @Body HashMap<String,String> pm){
		//System.out.println(text);
		System.out.println("pm:"+pm);

		String batchId = pm.get("batchId");;

		System.out.println("start");
		System.out.println("batchId:"+batchId);
		System.out.println();


		Thread thread = new Thread(new Runnable() {
			@Override
			public void run() {
				long start = System.currentTimeMillis();
				Connection conn = null;
				try {
					conn = getConnection();
					int i = 0;
					//判断批次是否存在
					System.out.println("查询批次..");
					while(!getBatch(conn,batchId)){
						System.out.println("未查询到批次..");
						Thread.sleep(1000);
						i++;
						if(i >= 100){
							break;
						}
					}
					//插入批次
					//String insertBatchSql = "insert into estate_import_batch (id,version) values(nextval('hibernate_sequence'),0)";
					//Long batchId = insert(conn,insertBatchSql);
					//System.out.println("batchId:"+batchId);
					////获取数据 插入更新
					//List<String> rs = getIds(conn,params);
					System.out.println("查询字典");
					Long evaluateStatueId = getDictId(conn, "com.cindata.dictionary.ExecStatusDict","未执行");
					Long typeId = getDictId(conn, "com.cindata.dictionary.CollateralType","房地产");
					System.out.println(evaluateStatueId);
					System.out.println("执行更新");
					String params = "";
					String name = pm.get("name");
					String city = pm.get("city_id");
					String district = pm.get("district_id");
					String sourceAddress = pm.get("sourceAddress");
					String estateStatus = pm.get("estateStatus");
					String buildingType = pm.get("buildingType");
					String latestEvaluateAccount = pm.get("latestEvaluateAccount");
					String cashability = pm.get("cashability");
					String buildAreaStart = pm.get("buildAreaStart");
					String buildAreaEnd = pm.get("buildAreaEnd");
					String externalId = pm.get("externalId");
					String minValue = pm.get("minValue");
					String maxValue = pm.get("maxValue");
					String accountFilter = pm.get("accountSql");

					if(name != null && !name.trim().equals("") ){
						params = params +" and c.name like '%"+name+"%' ";
					}
					if(city != null && !city.trim().equals("") ){
						params = params +" and c.city_id = "+city+" ";
					}
					if(district != null && !district.trim().equals("") ){
						params = params +" and c.district_id = "+district+" ";
					}
					if(sourceAddress != null && !sourceAddress.trim().equals("") ){
						params = params +" and e.source_address like '%"+sourceAddress+"%' ";
					}
					if(estateStatus != null && !estateStatus.trim().equals("") ){
						params = params +" and e.estate_status_id = "+estateStatus+" ";
					}
					if(buildingType != null && !buildingType.trim().equals("") ){
						params = params +" and e.building_type_id = "+buildingType+" ";
					}
					if(latestEvaluateAccount != null && !latestEvaluateAccount.trim().equals("") ){
						params = params +" and e.latest_evaluate_account_id = "+latestEvaluateAccount+" ";
					}
					if(cashability != null && !cashability.trim().equals("") ){
						params = params +" and c.cashability_id = "+cashability+" ";
					}
					if(buildAreaStart != null && !buildAreaStart.trim().equals("") ){
						params = params +" and e.build_area >= "+buildAreaStart+" ";
					}
					if(buildAreaEnd != null && !buildAreaEnd.trim().equals("") ){
						params = params +" and e.build_area <= "+buildAreaEnd+" ";
					}
					if(externalId != null && !externalId.trim().equals("") ){
						params = params +" and e.external_id = '"+externalId+"' ";
					}
					if(minValue != null && !minValue.trim().equals("") ){
						params = params +" and c.cltv >= "+minValue+" ";
					}
					if(maxValue != null && !maxValue.trim().equals("") ){
						params = params +" and c.cltv < "+maxValue+" ";
					}
					if(accountFilter != null && !accountFilter.trim().equals("") ){
						params = params +"and c.account_id in "+accountFilter  ;
					}

					String insertSql = "insert into batch_estate(id, version, batch_id, estate_id,evaluate_statue_id)select nextval('hibernate_sequence'), 0,"+batchId+", estate_id,"+evaluateStatueId+"" +
							" from (select c.* from collateral c left join estate e on c.estate_id = e.id where c.estate_id is not null "+params+" and c.status = 'Completed' and c.type_id ="+typeId+" ) t ";
					System.out.println(insertSql);
					update(conn, insertSql);
					String updateSql = "update estate_import_batch set status_id="+evaluateStatueId+" where id="+batchId;
					System.out.println(updateSql); 
					update(conn, updateSql);
				} catch (Exception e) {
					e.printStackTrace();
				}finally{
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
				long end = System.currentTimeMillis();
				System.out.println("耗时:"+(end-start)+"ms.");
			}
		});

		thread.start();
		System.out.println("end");
		HashMap<String,String> map = new HashMap<String,String>();
		map.put("code","0");
		map.put("message","success");
		return map;
	}


	public Connection getConnection(){
		Connection connection = null;
		try {
			Class.forName("org.postgresql.Driver");
			connection = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}

	public List<String> getIds(Connection connection,String sql) {
		ResultSet resultSet = null;
		ArrayList<String> rs = new ArrayList<String>();
		try {
			SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			System.out.println("查询开始"+df.format(new Date()));
			Statement statement = connection.createStatement();

			resultSet = statement.executeQuery("select estate_id from collateral where 1 = 1 and  "+sql);
			while (resultSet.next()) {
				rs.add(resultSet.getString(1));
			}
			System.out.println("查询结束"+df.format(new Date()));
			System.out.println("记录数"+rs.size());
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try{
				resultSet.close();
			}catch(Exception e){

			}
		}
		return rs;
	}

	public int update(Connection connection,String sql) {
		int i =-1;
		Statement statement = null;
		try {
			statement = connection.createStatement();
			i = statement.executeUpdate(sql);
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try{
				statement.close();
			}catch(Exception e){

			}
		}
		return i;
	}

	public Long insert(Connection connection,String sql) {
		long id = 0;
		Statement statement = null;
		try {
			statement = connection.createStatement();
			ResultSet rs = statement.executeQuery(sql+" RETURNING id");
			if(rs.next()) {
				id = rs.getLong(1);
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try{
				statement.close();
			}catch(Exception e){

			}
		}
		return id;
	}

	public long getDictId(Connection connection,String className,String name){
		Long rs = 0L;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select id from data_dict where name = '"+name+"' and class = '"+className+"'");
			System.out.println("select id from data_dict where name = '"+name+"' and class = '"+className+"'");
			while (resultSet.next()) {
				rs = resultSet.getLong(1);
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try{
				resultSet.close();
				statement.close();
			}catch(Exception e){

			}
		}
		return rs;

	}

	public boolean getBatch(Connection connection,String batchId){
		boolean rs = false;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select count(1) from estate_import_batch where id = "+batchId);
			System.out.println("select count(1) from estate_import_batch where id = "+batchId);
			if(resultSet.next()){
				int i = resultSet.getInt(1);
				System.out.println(".."+i);
				if(i>0){
					rs = true;
					System.out.println("存在");
				}
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try{
				resultSet.close();
				statement.close();
			}catch(Exception e){

			}
		}
		return rs;

	}

}